package code;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import love.cq.util.StringUtil;

 

public class MySqlDb {
	
	private static Connection ct =null;
	
	
	 public static void init(String dataUrl,String username, String password) {
		 ct=EstablishMysqlConnection(dataUrl, username, password);
	 }
	/**
	 * 
	 * @param mySqlDbName1
	 * @param sql
	 * @param display1
	 * @return
	 */
	
	public static  List<Map<String, Object>> mysqlSelectDb( String sql,boolean display){
	 
		List<Map<String, Object>> result=new ArrayList<Map<String,Object>>();
		PreparedStatement ps=null;
		ResultSet rs=null;
	 
		 
		 if (ct==null) return null;
			try {
			ps=ct.prepareStatement(sql);
			rs=ps.executeQuery();
			if (display){
				HashMap<String, Object> map = new HashMap<String, Object>();
				for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
					HashMap<String, Object> displayMap = new HashMap<String, Object>();
					displayMap.put("precision", rs.getMetaData().getPrecision(i));
					displayMap.put("type", rs.getMetaData().getColumnTypeName(i));
					displayMap.put("scale", rs.getMetaData().getScale(i));
					//rs.getMetaData().getColumnLabel(column)
					map.put(rs.getMetaData().getColumnName(i), displayMap);
				}
				result.add(map);
			}
			while (rs.next()){
 
				HashMap<String, Object> map = new HashMap<String, Object>();
				for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
 
					if (rs.getMetaData().getColumnTypeName(i).trim().toLowerCase().contains("char") || rs.getMetaData().getColumnTypeName(i).trim().toLowerCase().contains("text") ){
						if ( rs.getString(i)==null || rs.getString(i).trim().equals(null) || rs.getString(i).toString().trim().equals("null")){
							map.put(rs.getMetaData().getColumnLabel(i), "");
						} else{
							map.put(rs.getMetaData().getColumnLabel(i), rs.getString(i).trim());
						}
					}else if (rs.getMetaData().getColumnTypeName(i).toLowerCase().contains("datetime")) {
						try {
							java.sql.Timestamp date = (java.sql.Timestamp)rs.getTimestamp(i);
							if   (date   !=   null) {
								DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");  
						 
								map.put(rs.getMetaData().getColumnLabel(i), (String)df.format(date));
							}else{
								map.put(rs.getMetaData().getColumnLabel(i), "");
							}
						} catch (Exception e) {
							map.put(rs.getMetaData().getColumnLabel(i), "");
							System.out.println(e.getMessage()+"---"+rs.getMetaData().getColumnName(i));
						}
					} else if (rs.getMetaData().getColumnTypeName(i).toLowerCase().contains("int")) {
						if ( rs.getObject(i)!=null){
							map.put(rs.getMetaData().getColumnLabel(i), rs.getInt(i));
						}else{
							map.put(rs.getMetaData().getColumnLabel(i), 0);
							
						}
					} else if (rs.getMetaData().getColumnTypeName(i).toLowerCase().contains("real") || rs.getMetaData().getColumnTypeName(i).toLowerCase().contains("money")
							 || rs.getMetaData().getColumnTypeName(i).toLowerCase().contains("numeric") || rs.getMetaData().getColumnTypeName(i).toLowerCase().contains("float")) {
						if ( rs.getObject(i)!=null){
						map.put(rs.getMetaData().getColumnLabel(i), rs.getFloat(i));
						}else{
							map.put(rs.getMetaData().getColumnLabel(i), 0);
							
						}
					} else if (rs.getMetaData().getColumnTypeName(i).toLowerCase().contains("image")) {
						map.put(rs.getMetaData().getColumnLabel(i), rs.getBinaryStream(i));
					} else if (rs.getMetaData().getColumnTypeName(i).toLowerCase().contains("bit")) {
						map.put(rs.getMetaData().getColumnLabel(i), rs.getBoolean(i));
						
					}else{
					
						map.put(rs.getMetaData().getColumnLabel(i), rs.getObject(i));
					}
				}
				if (!map.equals(null)){
					result.add(map);
				}
			}
			//			}
		} catch (Exception e) {
			e.printStackTrace();
			//System.out.println(e.getMessage()+"---sql:"+sql);
			HashMap<String, Object> map = new HashMap<String, Object>();
			map.put("err",e.getMessage()+"");
			map.put("sql",sql);
			result.add(map);
		 
		}finally{
			close(ps,rs);
		}
		//}
		return result;
	}
	
	private static void close(PreparedStatement ps,ResultSet rs){

		try {

			if(rs!=null){

				rs.close();
				rs=null;//
			}
			if(ps!=null){

				ps.close();
				ps=null;
			}


		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	private static  Connection EstablishMysqlConnection(String dataUrl,String username, String password ){
  		Connection conn=null;
		String driverName = "com.mysql.jdbc.Driver"; // JDBC
		try {
			Class.forName(driverName).newInstance();
			conn=DriverManager.getConnection(dataUrl,username,password);
			System.out.println("mysql成功连接数据库！");
		} catch (Exception ex) {
		 
			ex.printStackTrace();
		}
	
		return conn;
    	
    }
	public static List<Map<String, Object>> mysqlSelectDb(String sql) {
		// TODO Auto-generated method stub
		return mysqlSelectDb(sql, false);
	}
	
	/**
	 * 
	 * 第一条SQL的ID  后续可替换 ${id} 
	 * 对mysql数据库
	 *按sql语句修改数据库包括插入删除修改
	 *param sql 需要执行的sql语句
	 *param dbName 对应数据库
	 *return 返回1正常否则 返回错误信息
	 **/
	public static Long mysqlChangeDb(String sqlStr){
	 
		//Connection ct= EstablishMysqlConnection();
			PreparedStatement ps=null;
			ResultSet rs=null;
			String[] sqls=sqlStr.split(";");
			String sql = "";
			Long id=null;
		try {
			ct.setAutoCommit(false);
			for (int i = 0; i < sqls.length; i++) {
				sql = sqls[i];
				if(StringUtil.isBlank(sql))continue;
				
				if(id!=null&&sql.contains("${id}")) {
					sql=sql.replaceAll("\\$\\{id\\}", id+"");
				}
				System.out.println("sql:"+sql);
		     	ps = ct.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
				ps.executeUpdate();
				ResultSet res=	ps.getGeneratedKeys();
				if(res.next()&&id==null ) {
					 long j=res.getLong(1);
					  if(j!=0)id=j;
				
				}	 
			}
			ct.commit();
			return id;
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				ct.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			return null;
		}finally{
			close(ps,rs);
			try {
				ct.setAutoCommit(true);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	

}
